<?php

class wms_mdl_deliverycomp extends dbeav_model{

  
    public function searchOptions(){
        $parentOptions = parent::searchOptions();
        $childOptions = array(
            'deliverynote_bn'=>app::get('base')->_('OMS送货单'),
        );
        return $Options = array_merge($parentOptions,$childOptions);
    }

    public function _filter($filter,$tableAlias=null,$baseWhere=null)
    {   
        #AX订单号
        if(isset($filter['deliverynote_bn'])){
            $deliverynote_bn = $filter['deliverynote_bn'];
            $sql = "select deliverycomp_id from sdb_wms_deliverycomp_line  where  deliverynote_bn= '$deliverynote_bn'";
            $_rows = $this->db->select($sql);
            if(!empty($_rows))
            {
                foreach($_rows as $_orders){
                    $deliverycomp_id[] = $_orders['deliverycomp_id'];
                }
                $where .= ' AND id IN ('.implode(',', $deliverycomp_id).')';
                unset($filter['deliverynote_bn']);
            }

        }

        return parent::_filter($filter,$tableAlias,$baseWhere).$where;
    }

    public function getCustomerList($shop_id){
       $sqlWhere='';
       if(!empty($shop_id) && count($shop_id)>0){
            $ids=join($shop_id,"','");
            $ids="'".$ids."'";
             $sqlWhere.=' and shop_id in ('.$ids.') ';
       }
        $sql="SELECT customername,shortname  from sdb_ome_shop
            where active='true' ".$sqlWhere."
            GROUP BY customername,shortname";
        $customerList = kernel::database()->select($sql);
        return $customerList;
    }
    function getLoginameList(){
       
        $sql="select name as logi_name from sdb_ome_dly_corp";
        $loginameList = kernel::database()->select($sql);
        return $loginameList;
    }
    function getBranchnameList(){
       
        $sql="select name from sdb_ome_branch";
        $branchnameList = kernel::database()->select($sql);
        return $branchnameList;
    }

    function getDeliveryInfo($order_id) {

        $sql = "SELECT odo.order_id,d.* , axshipno ,axorderno,dnl.status as dnl_status,b.branch_bn,b.name
                FROM sdb_wms_deliverycomp dc
                LEFT JOIN sdb_wms_deliverycomp_line dcl on (dc.id = dcl.deliverycomp_id)
                LEFT JOIN sdb_wms_deliverynote dn on (dcl.deliverynote_id = dn.id )
                LEFT JOIN sdb_wms_deliverynote_line dnl on ( dn.id = dnl.deliverynote_id )
                LEFT JOIN sdb_wms_delivery d on (dnl.delivery_id = d.delivery_id)
                LEFT JOIN sdb_ome_delivery od on (od.delivery_bn=d.outer_delivery_bn)
                LEFT JOIN sdb_ome_delivery_order odo on (odo.delivery_id=od.delivery_id)
                LEFT JOIN sdb_ome_branch b on (b.branch_id=d.branch_id)
                where dc.id = '$order_id' ";
        $rows = $this->db->select($sql);
        return $rows;

    }
    //备货汇总报表所有
    function GetReportData($start_date,$end_date,$orderform_bn,$pickinglist_bn,$logi_name,$name) {

        $whereSql='';
        if(!empty($start_date)){
            //$whereSql .="ANd D.create_time>'$start_date'  and D.create_time<= '$end_date' ";
            $whereSql .="AND B.deliverynote_time>'".strtotime($start_date)."' and B.deliverynote_time<='".strtotime($end_date)."' ";
        }
        if(!empty($orderform_bn))
        {
            $ofsql = "SELECT b.delivery_id from sdb_wms_orderform a
                    inner join sdb_wms_orderform_delis b on a.id=b.orderform_id
                    where a.orderform_bn=$orderform_bn'";
            $_rows = $this->db->select($ofsql);
            if(!empty($_rows)){
                foreach($_rows as $_orders){
                    $delivery_id[] = $_orders['delivery_id'];
                }
                $whereSql .= ' AND b.delivery_id IN ('.implode(',', $delivery_id).') ';
            }
        }
        if(!empty($pickinglist_bn))
        {
            $plsql = "SELECT b.delivery_id from sdb_wms_pickinglist a
                    inner join sdb_wms_pickinglist_line b on a.id=b.pickinglist_id
                    where a.pickinglist_bn=$pickinglist_bn'";
            $_rows = $this->db->select($plsql);
            if(!empty($_rows)){
                foreach($_rows as $_orders){
                    $delivery_id[] = $_orders['delivery_id'];
                }
                $whereSql .= ' AND B.delivery_id IN ('.implode(',', $delivery_id).') ';
            }
        }
        if(!empty($logi_name)){
            $whereSql.= "AND b.logi_name='$logi_name'";
        }
        if(!empty($name)){
            $whereSql.= "AND ob.name='$name'";
        }
		$sql = "SELECT * from (
                    select A.customername,N.material_bn,B.logi_name,ob.`name`,SUM(it.number*M.volume) as tovolume,count(B.delivery_id) qty from sdb_ome_shop A
                    inner join sdb_wms_delivery B on A.shop_id=B.shop_id
                    LEFT JOIN sdb_wms_delivery_items it on B.delivery_id=it.delivery_id
                    LEFT JOIN sdb_material_basic_material N on N.material_bn=it.bn
                    LEFT JOIN sdb_material_basic_material_ext M on M.bm_id=N.bm_id
                    LEFT JOIN sdb_ome_branch ob on ob.branch_id=B.branch_id
                    where  1=1 and B.is_form='true' and B.is_ship='true' ".$whereSql."
                    group by A.customername,N.material_bn
                )T ORDER BY T.qty desc";

        $rows = $this->db->select($sql);
        return $rows;
    }

   //备货汇总报表所有
    function GetReportLogi($start_date,$end_date,$orderform_bn) {
        $whereSql='';
        if(!empty($start_date)){
           // $whereSql .="AND D.create_time>'$start_date' and D.create_time<='$end_date' ";
             $whereSql .="AND D.create_time>'".strtotime($start_date)."' and D.create_time<='".strtotime($end_date)."' ";
        }
        if(!empty($orderform_bn)){
            $whereSql.= "AND D.orderform_bn='$orderform_bn'";
        }
                $sql = "select * from (
                    select B.logi_name,SUM(it.number*M.volume) as tovolume,count(B.delivery_id) qty from sdb_wms_delivery B
                                        inner join sdb_wms_orderform_delis C on B.delivery_id=C.delivery_id
                                        inner join sdb_wms_orderform D on C.orderform_id=D.id
                                        LEFT JOIN sdb_wms_delivery_items it on B.delivery_id=it.delivery_id
                                        LEFT JOIN sdb_material_basic_material N on N.material_bn=it.bn
                                        LEFT JOIN sdb_material_basic_material_ext M on M.bm_id=N.bm_id
                                        where  D.`status` = 'confirmed'
                                        ".$whereSql."
                    group by B.logi_name
                )T ORDER BY T.qty desc";
        $rows = $this->db->select($sql);
        return $rows;
    }
    function GetOrderSumData_ordreformreport($start_date,$end_date,$orderform_bn,$pickinglist_bn,$logi_name,$name) 
    {
        if(!empty($start_date))
        {
            $whereSql .="AND B.deliverynote_time>'".$start_date."' and B.deliverynote_time<='".$end_date."' ";
            //$whereSql .="AND B.deliverynote_time>'".strtotime($start_date)."' and B.deliverynote_time<='".strtotime($end_date)."' ";
        }
       
        if(!empty($orderform_bn))
        {
            $ofsql = "SELECT b.delivery_id from sdb_wms_orderform a
                    inner join sdb_wms_orderform_delis b on a.id=b.orderform_id
                    where a.orderform_bn='$orderform_bn'";
            $_rows = $this->db->select($ofsql);
            if(!empty($_rows)){
                foreach($_rows as $_orders){
                    $delivery_id[] = $_orders['delivery_id'];
                }
                $whereSql .= ' AND b.delivery_id IN ('.implode(',', $delivery_id).') ';
            }
        }
        if(!empty($pickinglist_bn))
        {
            $plsql = "SELECT b.delivery_id from sdb_wms_pickinglist a
                    inner join sdb_wms_pickinglist_line b on a.id=b.pickinglist_id
                    where a.pickinglist_bn='$pickinglist_bn'";
            $_rows = $this->db->select($plsql);
            if(!empty($_rows)){
                foreach($_rows as $_orders){
                    $delivery_id[] = $_orders['delivery_id'];
                }
                $whereSql .= ' AND B.delivery_id IN ('.implode(',', $delivery_id).') ';
            }
        }

        if(!empty($logi_name)){
            $whereSql.= "AND b.logi_name='$logi_name'";
        }
        if(!empty($name)){
            $whereSql.= "AND ob.name='$name'";
        }
        $sql = "SELECT * from (
            select A.customername,N.material_bn,B.logi_name,ob.`name`,SUM(it.number*M.volume) as tovolume,count(B.delivery_id) qty from sdb_ome_shop A
            inner join sdb_wms_delivery B on A.shop_id=B.shop_id
            LEFT JOIN sdb_wms_delivery_items it on B.delivery_id=it.delivery_id
            LEFT JOIN sdb_material_basic_material N on N.material_bn=it.bn
            LEFT JOIN sdb_material_basic_material_ext M on M.bm_id=N.bm_id
            LEFT JOIN sdb_ome_branch ob on ob.branch_id=B.branch_id
            where  1=1 and B.is_form='true' and B.is_ship='true' ".$whereSql."
            group by A.customername,N.material_bn
        )T ORDER BY T.qty desc";
        $rows = $this->db->select($sql);
        foreach($rows as $key=>$value) {
                //if ($value['material_bn'] == $v['order_items_bn'] && $value['shop_name'] == $v['shop_name']) {
                //    $delivery_detail_number += $v['delivery_detail_number'];
               // }
            $rows[$key]['customername'] =  $value['customername'];
            $rows[$key]['material_bn'] =  $value['material_bn'];
            $rows[$key]['logi_name'] =  $value['logi_name'];
            $rows[$key]['name'] =  $value['name'];
            $rows[$key]['qty'] =  $value['qty'];
            $rows[$key]['tovolume'] =  $value['tovolume'];
            }
           return $rows;

        }

   function GetReportLogiData($start_date,$end_date) {
       $whereSql='';
        if(!empty($start_date)){
             $whereSql .="AND swd.delivery_time>'".strtotime($start_date)."' and swd.delivery_time<='".strtotime($end_date)."' ";
        }
        $sql = "select swd.delivery_bn,FROM_UNIXTIME(swd.delivery_time, '%Y-%m-%d %H:%i:%S') as delivery_time,swde.logi_no,swd.weight,swd.ship_name,swd.ship_mobile,
                       swd.ship_tel,str_replace('/', '-', explode(':', swd.ship_area)),swd.ship_addr
                       from sdb_wms_delivery  swd
                       LEFT JOIN sdb_wms_delivery_bill swde on swde.delivery_id=swd.delivery_id
                       where swd.logi_name like '%邮政快递%'
                        ".$whereSql."
                 ORDER BY swd.weight";
        $rows = $this->db->select($sql);
        return $rows;
    }

     function GetSData_logireport($start_date,$end_date,$shop_id,$logi_name,$branch_id) 
     {
       $whereSql='';
        if(!empty($start_date)){
             $whereSql .="AND swd.delivery_time>'".strtotime($start_date)."' and swd.delivery_time<='".strtotime($end_date)."' ";
        }
        if(!empty($logi_name))
        {
            $whereSql.=" and swd.logi_id=".$logi_name;
        }
        if(!empty($shop_id))
        {
            $whereSql.=" and swd.shop_id='".$shop_id."'";
        }
        if(!empty($branch_id))
        {
            $whereSql.= " and bh.branch_id = '".$branch_id."'";
        }
        $sql = "select f.shop_bn,f.name shop_name,swd.delivery_bn,bh.name b_name, FROM_UNIXTIME(swd.delivery_time, '%Y-%m-%d %H:%i:%S') as delivery_time,swde.logi_no,swd.weight,swd.ship_name,swd.ship_mobile,
                       swd.ship_tel,swd.ship_area,swd.ship_addr
                       from sdb_wms_delivery  swd
                       inner join sdb_ome_branch bh on swd.branch_id=bh.branch_id and bh.owner='1'
                       LEFT JOIN sdb_wms_delivery_bill swde on swde.delivery_id=swd.delivery_id
                       inner join sdb_ome_shop f on swd.shop_id=f.shop_id 
                       where 1=1
                        ".$whereSql." 
                 ORDER BY swd.weight";
                 //var_dump($sql);
        $rows = $this->db->select($sql);

        foreach($rows as $key=>$value) {
            $area = explode(':', $value['ship_area']);
            $region_id = $area[1];
            $region =str_replace('/', '-', $region_id);

            $rows[$key]['shop_bn'] =  $value['shop_bn'];
            $rows[$key]['shop_name'] =  $value['shop_name'];
            $rows[$key]['delivery_bn'] =  $value['delivery_bn'];
            $rows[$key]['delivery_time'] =  $value['delivery_time'];
            $rows[$key]['logi_no'] =  $value['logi_no'];
            $rows[$key]['weight'] =  $value['weight'];
            $rows[$key]['ship_name'] =  $value['ship_name'];
            $rows[$key]['ship_mobile'] =  $value['ship_mobile'];
            $rows[$key]['ship_tel'] =  $value['ship_tel'];
            $rows[$key]['ship_area'] = preg_replace('/\r|\n|\t/', '', $region); //$region;
            $rows[$key]['ship_addr'] = preg_replace('/\r|\n|\t/', '', $value['ship_addr']);  //$value['ship_addr'];
            $rows[$key]['b_name'] =  $value['b_name'];

            }
           return $rows;

        }

    function GetOrderSumData($shop_id,$customername,$conditiondate,$paystart,$payend)
    {
        $where = " wd.status=3 AND sooi.`delete` != 'true' AND ";
        if($shop_id){
//            $where .= " sosh.shop_id ='".$shop_id."' AND ";
            $shop_id_tmp = implode("','", $shop_id);
            if(!empty($shop_id_tmp)){
                $shop_id = $shop_id_tmp;
            }
            $where .= " sosh.shop_id IN ('" . $shop_id . "') AND ";
        }
        if(!empty($customername)){
            $where .= " sosh.customername ='".$customername."' AND ";
        }

        if($conditiondate == 0)
        {
            $where .=" soo.paytime >= '".strtotime($paystart)."' and soo.paytime <= '".strtotime($payend)."' ";
        }else if($conditiondate==1)
        {
            $where .=" wd.delivery_time >= '".strtotime($paystart)."' and wd.delivery_time <= '".strtotime($payend)."' ";
        }
        $sql = " SELECT
                soo.`order_id`,
                soo.`order_bn`,
                soo.`status` AS 'orders_status',
                soo.`pay_status`,
                soo.`paytime`,
                soo.`mark_text` ,
                soo.`total_amount`,
                soo.`cost_item`,
                soo.`cost_freight`,
                soo.`cost_protect`,
                soo.`cost_tax`,
                soo.`discount`,
                soo.`pmt_goods`,
                soo.`pmt_order`,
                soo.`payed`,

                sooi.`name` AS 'order_items_name',
                sooi.`bn` AS 'order_items_bn',
                sooi.`amount` AS 'order_items_amount',
                sooi.`cost` AS 'order_items_cost',
                sooi.`price` AS 'order_items_price',
                sooi.`sale_price` AS 'order_items_sale_price',
                sooi.`pmt_price` AS 'order_items_pmt_price',
                sooi.`nums` AS 'order_items_nums' ,
                sooi.`sendnum` AS 'order_items_sendnum' ,
                sooi.`return_num` AS 'order_items_return_num' ,

                sodid.`amount` AS 'delivery_detail_amount' ,
                sodid.`price` AS 'delivery_detail_price' ,
                sodid.`number` AS 'delivery_detail_number' ,
                sodid.order_item_id,

                sosh.`name` AS 'shop_name' ,
                wd.`delivery_id` AS 'wms_delivery_id',
                wdb.`logi_no` ,
                wd.`logi_name` ,
                wd.`status` ,
                wd.`delivery_bn` ,
                smbme.`unit` AS 'unit' , '' AS '退货确认时间'

                FROM sdb_ome_delivery_items_detail AS sodid
                LEFT JOIN sdb_ome_order_items AS sooi ON sodid.order_item_id = sooi.item_id
                LEFT JOIN sdb_ome_delivery_items AS sodi ON sodi.item_id = sodid.delivery_item_id
                LEFT JOIN sdb_ome_delivery AS sod ON sod.delivery_id = sodi.delivery_id
                LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sodid.order_id
                LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
                LEFT JOIN sdb_wms_delivery AS wd ON wd.outer_delivery_bn = sod.delivery_bn
                LEFT JOIN sdb_wms_delivery_bill AS wdb ON (wdb.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sodid.product_id)
                WHERE 1=1 AND wd.`status`=3 AND sooi.`delete` != 'true' AND " . $where . " AND (sod.parent_id =0 or sod.parent_id is NULL)
                ";
        // echo $sql."<hr>";
        // die('');
        $rows_note = $this->db->select($sql);
        foreach($rows_note as $key_note=>$value_note){
            $rows_note_order_id[] =  $value_note['order_id'];
        }
        $rows_note_order_id_string = implode(",", $rows_note_order_id);


        $where = " soo.order_id IN (" . $rows_note_order_id_string . ") ";

        $sql = "SELECT sosh.name AS 'shop_name',sooi.bn AS 'material_bn',sooi.name AS 'material_name',smbme.unit,SUM(sooi.nums) AS qty,SUM(sooi.sendnum) AS qty1,SUM(sooi.sendnum) AS qty2,(SUM(sooi.nums) - SUM(sooi.sendnum)) AS qty3,SUM(sooi.return_num) AS qty4,GROUP_CONCAT(soo.order_bn SEPARATOR '|') AS 'order_bns' FROM sdb_ome_order_items AS sooi LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sooi.order_id LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sooi.product_id) WHERE 1=1 AND sooi.`delete` != 'true' AND soo.`status` <> 'dead' AND {$where} GROUP BY sosh.shop_id,sooi.bn ";
        //echo $sql."<hr>";
        $rows = $this->db->select($sql);

        $delivery_detail_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_detail_number = 0;
            foreach ($rows_note as $k => $v) {
                if ($value['material_bn'] == $v['order_items_bn'] && $value['shop_name'] == $v['shop_name']) {
                    $delivery_detail_number += $v['delivery_detail_number'];
                }
            }

            $rows[$key]['qty1'] = $delivery_detail_number;
            $rows[$key]['qty2'] = $delivery_detail_number;
            $rows[$key]['qty3'] = $value['qty'] - $delivery_detail_number;
        }

        //查询打印面单量
        $sql_print = "SELECT s.`name`,d.delivery_bn,it.bn,it.number as send_nums
        FROM sdb_ome_print_queue AS q
        LEFT JOIN sdb_ome_print_queue_items qit ON q.ident = qit.ident
        LEFT JOIN sdb_wms_delivery d ON qit.delivery_id = d.delivery_id
        LEFT JOIN sdb_wms_delivery_items it ON d.delivery_id = it.delivery_id
        LEFT JOIN sdb_ome_delivery sod ON d.outer_delivery_bn = sod.delivery_bn
        LEFT JOIN sdb_ome_delivery_order sodo ON sodo.delivery_id = sod.delivery_id
        LEFT JOIN sdb_ome_orders soo ON sodo.order_id = soo.order_id
        LEFT JOIN sdb_ome_shop s ON s.shop_id = d.shop_id
        WHERE {$where}";
        //echo $sql."<hr>";
        $rows_print = $this->db->select($sql_print);

        $delivery_print_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_print_number = 0;
            foreach ($rows_print as $k => $v) {
                if ($value['material_bn'] == $v['bn'] && $value['shop_name'] == $v['name']) {
                    $delivery_print_number += $v['send_nums'];
                }
            }
            $rows[$key]['print_nums'] = $delivery_print_number;
        }
        return $rows;
    }

    function GetOrderSumData_detail($shop_id,$customername,$conditiondate,$paystart,$payend)
    {
        if($shop_id){
            $shop_id_tmp = implode("','", $shop_id);
            if(!empty($shop_id_tmp)){
                $shop_id = $shop_id_tmp;
            }
            $where .= "AND sosh.shop_id IN ('" . $shop_id . "')";
        }

        if(!empty($customername)){
            $where .= "AND sosh.customername = '".$customername."' ";
        }
        if($conditiondate == 0)
        {
            $sql = "SELECT o.order_id,it.bn as order_items_bn,sosh.`name` as shop_name,it.nums as delivery_detail_number FROM sdb_ome_order_items AS it
            LEFT JOIN sdb_ome_orders o ON o.order_id = it.order_id
            LEFT JOIN sdb_ome_shop sosh ON sosh.shop_id = o.shop_id
            WHERE sosh.AXCustomer !='' AND o.process_status != 'cancel'  AND o.status != 'dead' AND o.is_fail='false' AND it.`delete` = 'false'
            AND o.paytime >='".strtotime($paystart)."'  and o.paytime <= '".strtotime($payend)."' {$where} ";
        }
        $rows_note = $this->db->select($sql);
        foreach($rows_note as $key_note=>$value_note){
            $rows_note_order_id[] =  $value_note['order_id'];
        }
        $rows_note_order_id_string = implode(",", $rows_note_order_id);


        $where = " soo.order_id IN (" . $rows_note_order_id_string . ") ";

        $sql = "SELECT sosh.name AS 'shop_name',sooi.bn AS 'material_bn',sooi.name AS 'material_name',smbme.unit,SUM(sooi.nums) AS qty,SUM(sooi.sendnum) AS qty1,SUM(sooi.sendnum) AS qty2,(SUM(sooi.nums) - SUM(sooi.sendnum)) AS qty3,SUM(sooi.return_num) AS qty4,GROUP_CONCAT(soo.order_bn SEPARATOR '|') AS 'order_bns' FROM sdb_ome_order_items AS sooi LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sooi.order_id LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sooi.product_id) WHERE 1=1 AND sooi.`delete` != 'true' AND soo.`status` <> 'dead' AND {$where} GROUP BY sosh.shop_id,sooi.bn ";
        $rows = $this->db->select($sql);
        //echo $sql."<hr>";
        /*$delivery_detail_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_detail_number = 0;
            foreach ($rows_note as $k => $v) {
                if ($value['material_bn'] == $v['order_items_bn'] && $value['shop_name'] == $v['shop_name']) {
                    $delivery_detail_number += $v['delivery_detail_number'];
                }
            }

            $rows[$key]['qty1'] = $delivery_detail_number;
            $rows[$key]['qty2'] = $delivery_detail_number;
            $rows[$key]['qty3'] = $value['qty'] - $delivery_detail_number;
        }*/

        //查询打印面单量
        $sql_print = "SELECT s.`name`,d.delivery_bn,it.bn,it.number as send_nums
        FROM sdb_ome_print_queue AS q
        LEFT JOIN sdb_ome_print_queue_items qit ON q.ident = qit.ident
        LEFT JOIN sdb_wms_delivery d ON qit.delivery_id = d.delivery_id
        LEFT JOIN sdb_wms_delivery_items it ON d.delivery_id = it.delivery_id
        LEFT JOIN sdb_ome_delivery sod ON d.outer_delivery_bn = sod.delivery_bn
        LEFT JOIN sdb_ome_delivery_order sodo ON sodo.delivery_id = sod.delivery_id
        LEFT JOIN sdb_ome_orders soo ON sodo.order_id = soo.order_id
        LEFT JOIN sdb_ome_shop s ON s.shop_id = d.shop_id
        WHERE {$where}";
        //echo $sql."<hr>";
        $rows_print = $this->db->select($sql_print);

        $delivery_print_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_print_number = 0;
            foreach ($rows_print as $k => $v) {
                if ($value['material_bn'] == $v['bn'] && $value['shop_name'] == $v['name']) {
                    $delivery_print_number += $v['send_nums'];
                }
            }
            $rows[$key]['print_nums'] = $delivery_print_number;
        }
        return $rows;
    }

    function GetOrderSumData_orderform($shop_id,$customername,$conditiondate,$paystart,$payend,$dlybegin,$dlyend)
    {
        $whereshop ='';
        if($shop_id){
            $shop_id_tmp = implode("','", $shop_id);
            if(!empty($shop_id_tmp)){
                $shop_id = $shop_id_tmp;
            }
            $whereshop .= "AND s.shop_id IN ('" . $shop_id . "')";
        }

        if(!empty($customername)){
            $whereshop .= "AND s.customername = '".$customername."' ";
        }

        $sql_del = "SELECT de.delivery_id FROM sdb_wms_orderform od
        LEFT JOIN sdb_wms_orderform_delis del ON od.id = del.orderform_id
        LEFT JOIN sdb_wms_delivery de ON de.delivery_bn = del.delivery_bn
        LEFT JOIN sdb_ome_shop s ON s.shop_id = de.shop_id
        WHERE s.AXCustomer != '' AND od.`status` ='confirmed' AND od.`status` ='confirmed'  AND od.create_time >= '".strtotime($paystart)."' and od.create_time <= '".strtotime($payend)."' {$whereshop} ";
        $rows_del = $this->db->select($sql_del);
        foreach($rows_del as $key_note=>$value_note){
            $rows_note_deli_id[] =  $value_note['delivery_id'];
        }
        $rows_note_deli_id_string = implode(",", $rows_note_deli_id);
        $where .= " d.delivery_id IN (" . $rows_note_deli_id_string . ") ";

        $sql = "SELECT de.delivery_id,s.name as shop_name,it.bn as material_bn,it.product_name as material_name,smbme.unit,SUM(it.number) as qty,SUM(it.number) as qty1  FROM sdb_wms_orderform od
        LEFT JOIN sdb_wms_orderform_delis del ON od.id = del.orderform_id
        LEFT JOIN sdb_wms_delivery de ON de.delivery_bn = del.delivery_bn
        LEFT JOIN sdb_wms_delivery_items it ON de.delivery_id = it.delivery_id
        LEFT JOIN sdb_ome_shop s ON s.shop_id = de.shop_id
        LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = it.product_id)
        WHERE s.AXCustomer != '' AND od.`status` ='confirmed' AND od.`status` ='confirmed'  AND od.create_time >= '".strtotime($paystart)."' and od.create_time <= '".strtotime($payend)."' {$whereshop}  GROUP BY s.name,it.bn";

        //echo $sql."<hr>";
        $rows = $this->db->select($sql);

        //查询发货单量
        $sql = "SELECT s.name as shop_name,it.bn as material_bn,SUM(it.number) as qty2 FROM sdb_wms_delivery AS d
        LEFT JOIN sdb_wms_delivery_items it ON it.delivery_id = d.delivery_id
        LEFT JOIN sdb_ome_shop s ON s.shop_id = d.shop_id
        WHERE  d.delivery_time !='' AND {$where} GROUP BY s.`name`,it.bn";

        //echo $sql."<hr>";
        $rows_delivery = $this->db->select($sql);
        foreach($rows as $key=>$value) {

            foreach ($rows_delivery as $k => $v) {
                if ($value['material_bn'] == $v['material_bn'] && $value['shop_name'] == $v['shop_name']) {
                    $rows[$key]['qty2'] += $v['qty2'];
                }
            }
            $rows[$key]['qty3'] = $rows[$key]['qty'] - $rows[$key]['qty2'] ;
        }

        //查询打印面单量
        $sql_print = "SELECT s.`name`,d.delivery_bn,it.bn,it.number as send_nums
        FROM sdb_ome_print_queue AS q
        LEFT JOIN sdb_ome_print_queue_items qit ON q.ident = qit.ident
        LEFT JOIN sdb_wms_delivery d ON qit.delivery_id = d.delivery_id
        LEFT JOIN sdb_wms_delivery_items it ON d.delivery_id = it.delivery_id
        LEFT JOIN sdb_ome_delivery sod ON d.outer_delivery_bn = sod.delivery_bn
        LEFT JOIN sdb_ome_delivery_order sodo ON sodo.delivery_id = sod.delivery_id
        LEFT JOIN sdb_ome_orders soo ON sodo.order_id = soo.order_id
        LEFT JOIN sdb_ome_shop s ON s.shop_id = d.shop_id
        WHERE {$where}";
        //echo $sql."<hr>";
        $rows_print = $this->db->select($sql_print);

        $delivery_print_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_print_number = 0;
            foreach ($rows_print as $k => $v) {
                if ($value['material_bn'] == $v['bn'] && $value['shop_name'] == $v['name']) {
                    $delivery_print_number += $v['send_nums'];
                }
            }
            $rows[$key]['print_nums'] = $delivery_print_number;
        }
        return $rows;
    }

    function GetOrderSumData_test($shop_id,$conditiondate,$paystart,$payend)
    {
        if($shop_id){
             $sqlstr = " and osh.shop_id ='".$shop_id."'  ";
         }else{
            $sqlstr = "";
        }
        $sqlorder='';
         if($conditiondate==0)
         {
            $sqlorder .=" and os.paytime >= '".strtotime($paystart)."' and os.paytime <= '".strtotime($payend.' 23:59:59')."' ";
         }else if($conditiondate==1)
         {
            $sqlorder .=" and od.delivery_time >= '".strtotime($paystart)."' and od.delivery_time <= '".strtotime($payend.' 23:59:59')."' ";
         }
        $sql="select t1.shop_name,t1.material_bn,t1.material_name,t1.unit,sum(t1.qty) qty,sum(t1.qty1) qty1,sum(t1.qty2) qty2,sum(t1.qty1)-sum(t1.qty2) qty3,0 qty4
FROM (select t.shop_name,t.material_name,t.material_bn,t.unit,t.qty,0 qty1,0 qty2 from (
/*
SELECT osh.`name` as shop_name ,P.material_name,P.material_bn, pext.unit as unit,osi.nums AS qty,os.order_bn
FROM  sdb_wms_delivery as wd
LEFT JOIN sdb_ome_delivery as od on ( wd.outer_delivery_bn = od.delivery_bn )
LEFT JOIN sdb_ome_delivery_order as odo on ( od.delivery_id = odo.delivery_id )
LEFT JOIN sdb_ome_orders as os on (odo.order_id = os.order_id)
LEFT JOIN sdb_ome_order_items osi on (os.order_id=osi.order_id)
LEFT JOIN sdb_ome_sales as sos on (sos.order_id = os.order_id)
LEFT JOIN sdb_ome_shop as osh on ( os.shop_id = osh.shop_id )
LEFT JOIN sdb_material_basic_material AS P ON osi.product_id=P.bm_id
LEFT JOIN sdb_material_basic_material_ext AS pext ON P.bm_id=pext.bm_id
where 1=1 osi.delete!='true' $sqlstr $sqlorder )t
*/
SELECT DISTINCT osh.`name` AS shop_name ,P.material_name,P.material_bn, pext.unit AS unit,osi.nums qty,os.order_bn
FROM  sdb_wms_delivery AS wd
LEFT JOIN sdb_ome_delivery AS od ON ( wd.outer_delivery_bn = od.delivery_bn )
LEFT JOIN sdb_ome_delivery_order AS odo ON ( od.delivery_id = odo.delivery_id )
LEFT JOIN sdb_ome_orders AS os ON (odo.order_id = os.order_id)
LEFT JOIN sdb_ome_order_items osi ON (os.order_id=osi.order_id)
LEFT JOIN sdb_ome_sales AS sos ON (sos.order_id = os.order_id)
LEFT JOIN sdb_ome_shop AS osh ON ( os.shop_id = osh.shop_id )
LEFT JOIN sdb_material_basic_material AS P ON osi.product_id=P.bm_id
LEFT JOIN sdb_material_basic_material_ext AS pext ON P.bm_id=pext.bm_id
where 1=1 and osi.delete!='true' $sqlstr $sqlorder )t


union all

SELECT  osh.`name` as shop_name ,P.material_name,P.material_bn, pext.unit as unit, 0 qty,wdi.number qty1,0 qty2
FROM sdb_wms_delivery_items wdi
LEFT JOIN sdb_wms_delivery as wd on (wdi.delivery_id=wd.delivery_id)
LEFT JOIN sdb_ome_delivery as od on ( wd.outer_delivery_bn = od.delivery_bn )
LEFT JOIN sdb_ome_delivery_order as odo on ( od.delivery_id = odo.delivery_id )
LEFT JOIN sdb_ome_orders as os on (odo.order_id = os.order_id)
LEFT JOIN sdb_ome_order_items osi ON (os.order_id = osi.order_id)
LEFT JOIN sdb_ome_sales as sos on (sos.order_id = os.order_id)
LEFT JOIN sdb_ome_shop as osh on ( os.shop_id = osh.shop_id )
LEFT JOIN sdb_wms_delivery_bill wdb on (wdb.delivery_id = wd.delivery_id)
LEFT JOIN sdb_material_basic_material AS P ON wdi.product_id=P.bm_id
LEFT JOIN sdb_material_basic_material_ext AS pext ON P.bm_id=pext.bm_id
where 1=1 AND osi.delete <> 'true' $sqlstr $sqlorder

union ALL

SELECT osh.`name` as shop_name ,P.material_name,P.material_bn, pext.unit as unit, 0 qty,0 qty1,wdi.number qty2
FROM sdb_wms_delivery_items wdi
LEFT JOIN sdb_wms_delivery as wd on (wdi.delivery_id=wd.delivery_id)
LEFT JOIN sdb_ome_delivery as od on ( wd.outer_delivery_bn = od.delivery_bn )
LEFT JOIN sdb_ome_delivery_order as odo on ( od.delivery_id = odo.delivery_id )
LEFT JOIN sdb_ome_orders as os on (odo.order_id = os.order_id)
LEFT JOIN sdb_ome_order_items osi ON (os.order_id = osi.order_id)
LEFT JOIN sdb_ome_sales as sos on (sos.order_id = os.order_id)
LEFT JOIN sdb_ome_shop as osh on ( os.shop_id = osh.shop_id )
INNER JOIN sdb_wms_orderform_delis wof on (wof.delivery_id = wd.delivery_id)
INNER JOIN sdb_wms_orderform wo on(wo.id=wof.orderform_id)
INNER JOIN sdb_wms_delivery_bill wdb on (wdb.delivery_id = wd.delivery_id)
LEFT JOIN sdb_material_basic_material AS P ON wdi.product_id=P.bm_id
LEFT JOIN sdb_material_basic_material_ext AS pext ON P.bm_id=pext.bm_id
where 1=1 AND osi.delete <> 'true' $sqlstr $sqlorder )t1
group by t1.shop_name,t1.material_bn,t1.material_name,t1.unit

";
    var_dump($sql);
        $rows = $this->db->select($sql);
        return $rows;
    }

    function GetOrderSumData_test1($shop_id,$conditiondate,$paystart,$payend)
    {
        $where = "";
        if($shop_id){
            $where .= " sosh.shop_id ='".$shop_id."' AND ";
        }
        if($conditiondate == 0)
        {
            $where .=" soo.paytime >= '".strtotime($paystart)."' and soo.paytime <= '".strtotime($payend.' 23:59:59')."' ";
        }else if($conditiondate==1)
        {
            $where .=" od.delivery_time >= '".strtotime($paystart)."' and od.delivery_time <= '".strtotime($payend.' 23:59:59')."' ";
        }
        $sql = "SELECT
sosh.name AS 'shop_name',
sooi.bn AS 'material_bn',
sooi.name AS 'material_name',
smbme.unit,
SUM(sooi.nums) AS qty,
SUM(sooi.sendnum) AS qty1,
SUM(sooi.sendnum) AS qty2,
(SUM(sooi.nums) - SUM(sooi.sendnum)) AS qty3,
SUM(sooi.return_num) AS qty4,
GROUP_CONCAT(soo.order_bn SEPARATOR '|') AS 'order_bns'

FROM sdb_ome_order_items AS sooi
LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sooi.order_id
LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sooi.product_id)
WHERE 1=1 AND sooi.`delete` != 'true' AND soo.`status` <> 'dead' AND {$where}
GROUP BY sosh.shop_id,sooi.bn ";
//echo $sql."<hr>";

        $rows = $this->db->select($sql);

        foreach($rows as $key=>$value){
            $order_bn_array = explode('|', $value['order_bns']);
            $sqlstr = " soo.`order_bn` IN ( '". implode("','", $order_bn_array) ."' ) AND sooi.`bn` = '{$value['material_bn']}'";
            $sql = " SELECT
                soo.`order_id`,
                soo.`order_bn`,
                soo.`status` AS 'orders_status',
                soo.`pay_status`,
                soo.`paytime`,
                soo.`mark_text` ,
                soo.`total_amount`,
                soo.`cost_item`,
                soo.`cost_freight`,
                soo.`cost_protect`,
                soo.`cost_tax`,
                soo.`discount`,
                soo.`pmt_goods`,
                soo.`pmt_order`,
                soo.`payed`,

                sooi.`name` AS 'order_items_name',
                sooi.`bn` AS 'order_items_bn',
                sooi.`amount` AS 'order_items_amount',
                sooi.`cost` AS 'order_items_cost',
                sooi.`price` AS 'order_items_price',
                sooi.`sale_price` AS 'order_items_sale_price',
                sooi.`pmt_price` AS 'order_items_pmt_price',
                sooi.`nums` AS 'order_items_nums' ,
                sooi.`sendnum` AS 'order_items_sendnum' ,
                sooi.`return_num` AS 'order_items_return_num' ,

                sodid.`amount` AS 'delivery_detail_amount' ,
                sodid.`price` AS 'delivery_detail_price' ,
                sodid.`number` AS 'delivery_detail_number' ,
                sodid.order_item_id,

                osh.`name` AS 'shop_name' ,
                wd.`delivery_id` AS 'wms_delivery_id',
                wdb.`logi_no` ,
                wd.`logi_name` ,
                wd.`status` ,
                wd.`delivery_bn` ,
                wof.`last_modify`,
                smbme.`unit` AS 'unit' , '' AS '退货确认时间'

                FROM sdb_ome_delivery_items_detail AS sodid
                LEFT JOIN sdb_ome_order_items AS sooi ON sodid.order_item_id = sooi.item_id
                LEFT JOIN sdb_ome_delivery_items AS sodi ON sodi.item_id = sodid.delivery_item_id
                LEFT JOIN sdb_ome_delivery AS sod ON sod.delivery_id = sodi.delivery_id
                LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sodid.order_id
                LEFT JOIN sdb_ome_shop AS osh ON soo.shop_id = osh.shop_id
                LEFT JOIN sdb_wms_delivery AS wd ON wd.outer_delivery_bn = sod.delivery_bn
                LEFT JOIN sdb_wms_orderform_delis AS wof ON (wof.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_wms_delivery_bill AS wdb ON (wdb.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sodid.product_id)
                WHERE 1=1 AND wd.`status`=3 AND sooi.`delete` != 'true' AND " . $sqlstr . " AND (sod.parent_id =0 or sod.parent_id is NULL)
                ";
            //echo $sql."<hr>";

            $delivery_detail_number = 0;
            $rows_note = $this->db->select($sql);
            foreach($rows_note as $k=>$v){
                if($value['material_bn'] == $v['order_items_bn']) {
                    $delivery_detail_number += $v['delivery_detail_number'];
                }
            }

            $rows[$key]['qty1'] = $delivery_detail_number;
            $rows[$key]['qty2'] = $delivery_detail_number;
        }
        return $rows;
    }

    function GetOrderSumData_test2($shop_id,$conditiondate,$paystart,$payend)
    {
        $where = " wd.status=3 AND ";
        if($shop_id){
//            $where .= " sosh.shop_id ='".$shop_id."' AND ";
            $shop_id_tmp = implode("','", $shop_id);
            if(!empty($shop_id_tmp)){
                $shop_id = $shop_id_tmp;
            }
            $where .= " sosh.shop_id IN ('" . $shop_id . "') AND ";
        }
        if($conditiondate == 0)
        {
            $where .=" soo.paytime >= '".strtotime($paystart)."' and soo.paytime <= '".strtotime($payend.' 23:59:59')."' ";
        }else if($conditiondate==1)
        {
            $where .=" wd.delivery_time >= '".strtotime($paystart)."' and wd.delivery_time <= '".strtotime($payend.' 23:59:59')."' ";
        }
        $sql = " SELECT
                sodid.`delivery_item_id`,
                sodid.`order_id`,
                sodid.`order_item_id`,
                sodid.`number` AS 'delivery_detail_number' ,
                sodid.`bn` AS 'order_items_bn',
                sosh.`name` AS 'shop_name'

                FROM sdb_wms_delivery AS wd
                LEFT JOIN sdb_ome_delivery AS sod  ON wd.outer_delivery_bn = sod.delivery_bn
                LEFT JOIN sdb_ome_delivery_items_detail AS sodid ON sod.delivery_id = sodid.delivery_id
                LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sodid.order_id
                LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
                WHERE 1=1 AND " . $where . "  ";
        //echo $sql."<hr>";
        $rows_note = $this->db->select($sql);
        foreach($rows_note as $key_note=>$value_note){
            //$rows_note_order_id[] =  $value_note['order_id'];
            $rows_note_order_item_id[] =  $value_note['order_item_id'];
        }
        //$rows_note_order_id_string = implode(",", $rows_note_order_id);
        $rows_note_order_item_id_string = implode(",", $rows_note_order_item_id);


        //$where = " soo.order_id IN (" . $rows_note_order_id_string . ") ";
        $where = " sooi.item_id IN (" . $rows_note_order_item_id_string . ") ";

        $sql = "SELECT
sosh.name AS 'shop_name',
sooi.bn AS 'material_bn',
sooi.name AS 'material_name',
smbme.unit,
SUM(sooi.nums) AS qty,
SUM(sooi.sendnum) AS qty1,
SUM(sooi.sendnum) AS qty2,
(SUM(sooi.nums) - SUM(sooi.sendnum)) AS qty3,
SUM(sooi.return_num) AS qty4,
GROUP_CONCAT(soo.order_bn SEPARATOR '|') AS 'order_bns'

FROM sdb_ome_order_items AS sooi
LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sooi.order_id
LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sooi.product_id)

WHERE 1=1 AND sooi.`delete` != 'true' AND soo.`status` <> 'dead' AND {$where}
GROUP BY sosh.shop_id,sooi.bn ";
        //echo $sql."<hr>";

        $rows = $this->db->select($sql);
        $delivery_detail_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_detail_number = 0;
            foreach ($rows_note as $k => $v) {
                if ($value['material_bn'] == $v['order_items_bn'] && $value['shop_name'] == $v['shop_name']) {
                    $delivery_detail_number += $v['delivery_detail_number'];
                }
            }

            $rows[$key]['qty1'] = $delivery_detail_number;
            $rows[$key]['qty2'] = $delivery_detail_number;
            $rows[$key]['qty3'] = $value['qty'] - $delivery_detail_number;
        }
        return $rows;
    }

    function GetOrderSumData_test3($shop_id,$conditiondate,$paystart,$payend)
    {
        $where = " ";
        if($conditiondate == 0)
        {
            $wd_status_1 = " ";
            $wd_status_2 = " ";
            $where .=" AND soo.paytime >= '".strtotime($paystart)."' and soo.paytime <= '".strtotime($payend.' 23:59:59')."' ";
        }else if($conditiondate==1)
        {
            $wd_status_1 = " WHERE tmp.wd_status IS NOT NULL";
            $wd_status_2 = " AND (wd.`status`=3 AND wd.`status` IS NOT NULL) ";
            $where .=" AND wd.delivery_time >= '".strtotime($paystart)."' and wd.delivery_time <= '".strtotime($payend.' 23:59:59')."' ";
        }
        //$where .= " AND sooi.`delete` != 'true' AND (sod.parent_id =0 OR sod.parent_id IS NULL) ";
        if($shop_id){
//            $where .= " sosh.shop_id ='".$shop_id."' AND ";
            $shop_id_tmp = implode("','", $shop_id);
            if(!empty($shop_id_tmp)){
                $shop_id = $shop_id_tmp;
            }
            $where .= " AND sosh.shop_id IN ('" . $shop_id . "')  ";
        }
        $sql = "
SELECT sosh.name AS 'shop_name'
, sooi.bn AS 'material_bn'
, sooi.name AS 'material_name'
, smbme.unit
, SUM(sooi.nums) AS qty
, SUM(sooi.sendnum) AS qty1
, SUM(sooi.sendnum) AS qty2
, (SUM(sooi.nums) - SUM(sooi.sendnum)) AS qty3
, SUM(sooi.return_num) AS qty4
, GROUP_CONCAT(soo.order_bn SEPARATOR '|') AS 'order_bns'
FROM sdb_ome_order_items AS sooi
LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sooi.order_id
LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sooi.product_id)
WHERE
1=1
AND sooi.`delete` != 'true'
AND soo.`status` <> 'dead'
AND sooi.item_id
IN (

SELECT DISTINCT order_item_id FROM (

SELECT sodid.`delivery_item_id` ,wd.status AS wd_status ,soo.`order_id` ,soo.`order_bn` ,
soo.`status` AS 'orders_status' ,soo.`pay_status`, soo.`paytime`, soo.`mark_text` , soo.`total_amount` ,
soo.`cost_item`, soo.`cost_freight`, soo.`cost_protect`, soo.`cost_tax` ,soo.`discount`, soo.`pmt_goods`,
soo.`pmt_order`, soo.`payed` ,
sooi.`name` AS 'order_items_name' ,sooi.`bn` AS 'order_items_bn' ,
sooi.`amount` AS 'order_items_amount' ,sooi.`cost` AS 'order_items_cost' ,sooi.`price` AS 'order_items_price' ,
sooi.`sale_price` AS 'order_items_sale_price' ,sooi.`pmt_price` AS 'order_items_pmt_price' ,
sooi.`nums` AS 'order_items_nums' ,sooi.`sendnum` AS 'order_items_sendnum' ,
sooi.`return_num` AS 'order_items_return_num' ,sodid.`amount` AS 'delivery_detail_amount' ,
sodid.`price` AS 'delivery_detail_price' ,sodid.`number` AS 'delivery_detail_number' ,
sodid.order_item_id ,sosh.`name` AS 'shop_name' ,wd.`delivery_id` AS 'wms_delivery_id' ,
wdb.`logi_no` , wd.`logi_name` , wd.`status` , wd.`delivery_bn` , wof.`last_modify` ,
smbme.`unit` AS 'unit' , '' AS '退货确认时间'
FROM sdb_ome_orders AS soo
LEFT JOIN sdb_ome_delivery_items_detail AS sodid ON (soo.order_id = sodid.order_id )
LEFT JOIN sdb_ome_order_items AS sooi ON (sodid.order_item_id = sooi.item_id AND sooi.`delete` != 'true' )
LEFT JOIN sdb_ome_delivery_items AS sodi ON sodi.item_id = sodid.delivery_item_id
LEFT JOIN sdb_ome_delivery AS sod ON (sod.delivery_id = sodi.delivery_id AND (sod.parent_id =0 OR sod.parent_id IS NULL))
LEFT JOIN sdb_wms_delivery AS wd ON wd.outer_delivery_bn = sod.delivery_bn ". $wd_status_2. "
LEFT JOIN sdb_wms_orderform_delis AS wof ON (wof.delivery_id = wd.delivery_id)
LEFT JOIN sdb_wms_delivery_bill AS wdb ON (wdb.delivery_id = wd.delivery_id)
LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sodid.product_id)
LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
WHERE 1=1
".$where."
AND sooi.`delete` != 'true'
AND (sod.parent_id =0 OR sod.parent_id IS NULL)

) AS tmp ".
$wd_status_1 .
") GROUP BY sosh.shop_id,sooi.bn
                ";
//echo $sql."<hr>";

        list($usec, $sec) = explode(" ", microtime());
        //echo date('Y-m-d H:i:s',$sec)." ".$usec."<hr/>";

        $rows = $this->db->select($sql);

        list($usec, $sec) = explode(" ", microtime());
        //echo date('Y-m-d H:i:s',$sec)." ".$usec."<hr/>";

        return $rows;
    }

    function GetOrderSumData_test4($shop_id,$conditiondate,$paystart,$payend)
    {
        $where = " ";
        if($conditiondate == 0)
        {
            $where .=" soo.paytime >= '".strtotime($paystart)."' and soo.paytime <= '".strtotime($payend.' 23:59:59')."' ";
        }else if($conditiondate==1)
        {
            $where .=" wd.delivery_time >= '".strtotime($paystart)."' and wd.delivery_time <= '".strtotime($payend.' 23:59:59')."' ";
        }
        if($shop_id){
//            $where .= " sosh.shop_id ='".$shop_id."' AND ";
            $shop_id_tmp = implode("','", $shop_id);
            if(!empty($shop_id_tmp)){
                $shop_id = $shop_id_tmp;
            }
            $where .= " sosh.shop_id IN ('" . $shop_id . "') AND ";
        }
        $sql = " SELECT  * FROM (

                SELECT
                soo.`order_id`,
                soo.`order_bn`,
                soo.`status` AS 'orders_status',
                soo.`pay_status`,
                soo.`paytime`,
                soo.`mark_text` ,
                soo.`total_amount`,
                soo.`cost_item`,
                soo.`cost_freight`,
                soo.`cost_protect`,
                soo.`cost_tax`,
                soo.`discount`,
                soo.`pmt_goods`,
                soo.`pmt_order`,
                soo.`payed`,

                sooi.`name` AS 'order_items_name',
                sooi.`bn` AS 'order_items_bn',
                sooi.`amount` AS 'order_items_amount',
                sooi.`cost` AS 'order_items_cost',
                sooi.`price` AS 'order_items_price',
                sooi.`sale_price` AS 'order_items_sale_price',
                sooi.`pmt_price` AS 'order_items_pmt_price',
                sooi.`nums` AS 'order_items_nums' ,
                sooi.`sendnum` AS 'order_items_sendnum' ,
                sooi.`return_num` AS 'order_items_return_num' ,

                sodid.`amount` AS 'delivery_detail_amount' ,
                sodid.`price` AS 'delivery_detail_price' ,
                sodid.`number` AS 'delivery_detail_number' ,
                sodid.order_item_id,

                sosh.`name` AS 'shop_name' ,
                wd.`delivery_id` AS 'wms_delivery_id',
                wdb.`logi_no` ,
                wd.`logi_name` ,
                wd.`status` AS 'wms_status',
                wd.`delivery_bn` ,
                wof.`last_modify`,
                smbme.`unit` AS 'unit' , '' AS '退货确认时间'

                FROM sdb_ome_delivery_items_detail AS sodid
                LEFT JOIN sdb_ome_order_items AS sooi ON sodid.order_item_id = sooi.item_id
                LEFT JOIN sdb_ome_delivery_items AS sodi ON sodi.item_id = sodid.delivery_item_id
                LEFT JOIN sdb_ome_delivery AS sod ON sod.delivery_id = sodi.delivery_id
                LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sodid.order_id
                LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
                LEFT JOIN sdb_wms_delivery AS wd ON wd.outer_delivery_bn = sod.delivery_bn AND (wd.`status`=3 AND wd.`status` IS NOT NULL)
                LEFT JOIN sdb_wms_orderform_delis AS wof ON (wof.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_wms_delivery_bill AS wdb ON (wdb.delivery_id = wd.delivery_id)
                LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sodid.product_id)
                WHERE 1=1 AND " . $where . " AND sooi.`delete` != 'true' AND (sod.parent_id =0 or sod.parent_id is NULL)

                ) AS tmp WHERE `wms_status`=3 AND `wms_status` IS NOT NULL
                ";
echo $sql."<hr>";
        $rows_note = $this->db->select($sql);


        list($usec, $sec) = explode(" ", microtime());
        echo date('Y-m-d H:i:s',$sec)." ".$usec."<hr/>";


        foreach($rows_note as $key_note=>$value_note){
            $rows_note_order_id[] =  $value_note['order_id'];
        }
        $rows_note_order_id_string = implode(",", $rows_note_order_id);


        $where = " soo.order_id IN (" . $rows_note_order_id_string . ") ";

        $sql = "SELECT
sosh.name AS 'shop_name',
sooi.bn AS 'material_bn',
sooi.name AS 'material_name',
smbme.unit,
SUM(sooi.nums) AS qty,
SUM(sooi.sendnum) AS qty1,
SUM(sooi.sendnum) AS qty2,
(SUM(sooi.nums) - SUM(sooi.sendnum)) AS qty3,
SUM(sooi.return_num) AS qty4,
GROUP_CONCAT(soo.order_bn SEPARATOR '|') AS 'order_bns'

FROM sdb_ome_order_items AS sooi
LEFT JOIN sdb_ome_orders AS soo ON soo.order_id = sooi.order_id
LEFT JOIN sdb_ome_shop AS sosh ON soo.shop_id = sosh.shop_id
LEFT JOIN sdb_material_basic_material_ext AS smbme ON (smbme.bm_id = sooi.product_id)

WHERE 1=1 AND sooi.`delete` != 'true' AND soo.`status` <> 'dead' AND {$where}
GROUP BY sosh.shop_id,sooi.bn ";
echo $sql."<hr>";
        $rows = $this->db->select($sql);

        list($usec, $sec) = explode(" ", microtime());
        echo date('Y-m-d H:i:s',$sec)." ".$usec."<hr/>";

        $delivery_detail_number = 0;
        foreach($rows as $key=>$value) {
            $delivery_detail_number = 0;
            foreach ($rows_note as $k => $v) {
                if ($value['material_bn'] == $v['order_items_bn'] && $value['shop_name'] == $v['shop_name']) {
                    $delivery_detail_number += $v['delivery_detail_number'];
                }
            }

            $rows[$key]['qty1'] = $delivery_detail_number;
            $rows[$key]['qty2'] = $delivery_detail_number;
            $rows[$key]['qty3'] = $value['qty'] - $delivery_detail_number;
        }


        list($usec, $sec) = explode(" ", microtime());
        echo date('Y-m-d H:i:s',$sec)." ".$usec."<hr/>";

        return $rows;
    }

	function GetlogistSumData($customername,$logi_name,$de_begin,$de_end){
		if($customername && $logi_name){
            $where .= "AND sle.customername = '".$customername."'";
			$sql_shortname = "SELECT shortname FROM sdb_ome_shop WHERE customername = '".$customername."'";
			$rows_shortname = $this->db->select($sql_shortname);
			$rows_customer[0]['customername'] = $customername;
			$rows_customer[0]['shortname'] = $rows_shortname[0]['shortname'];
			$rows_customer[0]['logi_name'] = $logi_name;
        }else if(!$customername && !$logi_name){
			$sql_customer = "SELECT sle.customername,sle.logi_name,s.shortname
			FROM sdb_logisticsaccounts_estimate AS sle
			LEFT JOIN sdb_ome_shop s ON s.customername = sle.customername
			WHERE sle.customername != '' AND sle.delivery_time >= '".strtotime($de_begin)."' AND sle.delivery_time <= '".strtotime($de_end)."' GROUP BY sle.customername,sle.logi_name";
			$rows_customer = $this->db->select($sql_customer);
		}else if($customername && !$logi_name){
			$sql_customer = "SELECT sle.customername,sle.logi_name,s.shortname
			FROM sdb_logisticsaccounts_estimate AS sle
			LEFT JOIN sdb_ome_shop s ON s.customername = sle.customername
			WHERE sle.customername != '' AND sle.delivery_time >= '".strtotime($de_begin)."' AND sle.delivery_time <= '".strtotime($de_end)."' AND sle.customername ='".$customername."' GROUP BY sle.customername,sle.logi_name";
			//echo $sql_customer;
			$rows_customer = $this->db->select($sql_customer);
		}else if(!$customername && $logi_name){
			$sql_customer = "SELECT sle.customername,sle.logi_name,s.shortname
			FROM sdb_logisticsaccounts_estimate AS sle
			LEFT JOIN sdb_ome_shop s ON s.customername = sle.customername
			WHERE sle.customername != '' AND sle.delivery_time >= '".strtotime($de_begin)."' AND sle.delivery_time <= '".strtotime($de_end)."' AND sle.logi_name ='".$logi_name."' GROUP BY sle.customername,sle.logi_name";
			$rows_customer = $this->db->select($sql_customer);
		}
		$sql = "SELECT  DISTINCT(sle.eid),sle.customername,sle.ship_province,sle.weight_qz,sle.logist_cost,sos.shortname,sle.logi_name,sle.eid,sle.delivery_cost_expect FROM sdb_logisticsaccounts_estimate AS sle
		LEFT JOIN sdb_ome_shop sos ON sos.customername = sle.customername
		WHERE delivery_time >= '".strtotime($de_begin)."' AND delivery_time <= '".strtotime($de_end)."' {$where} ";
        $rows = $this->db->select($sql);

		$datalist = array();
		foreach($rows_customer as $key => $value){
			$datalist[$key]['customername'] = $value['customername'];
			$datalist[$key]['shortname'] = $value['shortname'];
			$datalist[$key]['logi_name'] = $value['logi_name'];
			foreach($rows as $k => $v){
			$datalist[$key]['customername'] = $value['customername'];
			$datalist[$key]['shortname'] = $value['shortname'];
				if($value['customername'] == $v['customername'] && $value['logi_name'] == $v['logi_name']){
					if($v['ship_province'] == '新疆' || $v['ship_province'] == '新疆区' || $v['ship_province'] == '新疆维吾尔自治区'){
						$datalist[$key]['weight_xinjiang'] +=  1;
						$datalist[$key]['delivery_cost_expect_xinjiang'] +=  $v['delivery_cost_expect'];
						$datalist[$key]['cost_xinjiang'] +=  $v['logist_cost'];
						$datalist[$key]['special_cost'] +=  $v['logist_cost'];

					}else if($v['ship_province'] == '内蒙古'){
						$datalist[$key]['weight_neimeng'] +=  1;
						$datalist[$key]['delivery_cost_expect_neimeng'] +=  $v['delivery_cost_expect'];
						$datalist[$key]['cost_neimeng'] +=  $v['logist_cost'];
						$datalist[$key]['special_cost'] +=  $v['logist_cost'];
					}else if($v['ship_province'] == '西藏'){
						$datalist[$key]['weight_xizang'] +=  1;
						$datalist[$key]['delivery_cost_expect_xizang'] +=  $v['delivery_cost_expect'];
						$datalist[$key]['cost_xizang'] +=  $v['logist_cost'];
						$datalist[$key]['special_cost'] +=  $v['logist_cost'];
					}else if($v['ship_province'] == '海南' || $v['ship_province'] == '海南省'){
						$datalist[$key]['weight_hainan'] +=  1;
						$datalist[$key]['delivery_cost_expect_hainan'] +=  $v['delivery_cost_expect'];
						$datalist[$key]['cost_hainan'] +=  $v['logist_cost'];
						$datalist[$key]['special_cost'] +=  $v['logist_cost'];
					}else{
						if($v['weight_qz'] <= 3){
							$datalist[$key]['weight_lthan3'] +=  1;
							$datalist[$key]['delivery_cost_expect_lthan3'] +=  $v['delivery_cost_expect'];
							$datalist[$key]['cost_lthan3'] +=  $v['logist_cost'];
						}else if($v['weight_qz'] > 3 && $v['weight_qz'] <= 5){
							$datalist[$key]['weight_lthan5'] +=  1;
							$datalist[$key]['delivery_cost_expect_lthan5'] +=  $v['delivery_cost_expect'];
							$datalist[$key]['cost_lthan5'] +=  $v['logist_cost'];
						}else if($v['weight_qz'] > 5){
							$datalist[$key]['weight_than5'] +=  1;
							$datalist[$key]['delivery_cost_expect_than5'] +=  $v['delivery_cost_expect'];
							$datalist[$key]['cost_than5'] +=  $v['logist_cost'];
						}
						$datalist[$key]['common_cost'] += $v['logist_cost'];
					}
					$datalist[$key]['total_cost'] += $v['logist_cost'];
				}
			}

		}
        return $datalist;
	}
}